package com.lagou.studentsystem.dao.Impl;

import com.lagou.studentsystem.dao.StudentDao;
import com.lagou.studentsystem.model.Student;
import com.lagou.studentsystem.util.DBUtil;
import com.lagou.studentsystem.util.DruidUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements StudentDao {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    @Override
    public List<Student> getStudentAll() {
        try {
            //1.获取连接
            connection = DruidUtils.getConnection();
            //2.编写sql
            String sql = "select * from student";
            //3.创建PreparedStatement类型的引用
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            List<Student> studentList = new ArrayList<>();
            while (resultSet.next()){
                Student stu = new Student();
                stu.setStu_id(resultSet.getString("stu_id"));
                stu.setStu_name(resultSet.getString("stu_name"));
                stu.setStu_birthday(resultSet.getString("stu_birthday"));
                stu.setStu_email(resultSet.getString("stu_email"));
                stu.setStu_sex(resultSet.getString("stu_sex"));
                stu.setStu_remarks(resultSet.getString("stu_remarks"));
                studentList.add(stu);
            }
            return studentList;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DruidUtils.close(connection,preparedStatement,resultSet);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public int insertStudent(Student student) {
        try {
            //1.获取连接
            connection = DruidUtils.getConnection();
            //2.编写sql
            String sql = "insert into student values (?,?,?,?,?,?) ;";
            //3.创建PreparedStatement类型的引用
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,student.getStu_id());
            preparedStatement.setString(2,student.getStu_name());
            preparedStatement.setString(3,student.getStu_sex());
            preparedStatement.setString(4,student.getStu_birthday());
            preparedStatement.setString(5,student.getStu_email());
            preparedStatement.setString(6,student.getStu_remarks());
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DruidUtils.close(connection,preparedStatement);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    @Override
    public int deleteStudentById(String id) {
        try {
            //1.获取连接
            connection = DruidUtils.getConnection();
            //2.编写sql
            String sql = "delete from student where stu_id = ? ;";
            //3.创建PreparedStatement类型的引用
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,id);
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DruidUtils.close(connection,preparedStatement);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    @Override
    public int updateStudentById(String id,Student student) {
        try {
            //1.获取连接
            connection = DruidUtils.getConnection();
            //2.编写sql
            String sql = "update student set stu_id = ? ,stu_name = ?,stu_sex = ?,stu_birthday = ?,stu_email = ?,stu_remarks = ? where stu_id = ? ;";
            //3.创建PreparedStatement类型的引用
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,student.getStu_id());
            preparedStatement.setString(2,student.getStu_name());
            preparedStatement.setString(3,student.getStu_sex());
            preparedStatement.setString(4,student.getStu_birthday());
            preparedStatement.setString(5,student.getStu_email());
            preparedStatement.setString(6,student.getStu_remarks());
            preparedStatement.setString(7,id);
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DruidUtils.close(connection,preparedStatement);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    @Override
    public Student getStudentById(String id) {
        try {
            //1.获取连接
            connection = DruidUtils.getConnection();
            //2.编写sql
            String sql = "select * from student where stu_id = ? ";
            //3.创建PreparedStatement类型的引用
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,id);
            resultSet = preparedStatement.executeQuery();
            Student stu = new Student();
            while (resultSet.next()){
                stu.setStu_id(resultSet.getString("stu_id"));
                stu.setStu_name(resultSet.getString("stu_name"));
                stu.setStu_birthday(resultSet.getString("stu_birthday"));
                stu.setStu_email(resultSet.getString("stu_email"));
                stu.setStu_sex(resultSet.getString("stu_sex"));
                stu.setStu_remarks(resultSet.getString("stu_remarks"));
            }
            return stu;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DruidUtils.close(connection,preparedStatement,resultSet);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public List<Student> getStudentByName(String name) {
        try {
            //1.获取连接
            connection = DruidUtils.getConnection();
            //2.编写sql
            String sql = "select * from student where stu_name = ? ";
            //3.创建PreparedStatement类型的引用
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,name);
            resultSet = preparedStatement.executeQuery();
            List<Student> studentList = new ArrayList<>();
            Student stu = new Student();
            while (resultSet.next()){
                stu.setStu_id(resultSet.getString("stu_id"));
                stu.setStu_name(resultSet.getString("stu_name"));
                stu.setStu_birthday(resultSet.getString("stu_birthday"));
                stu.setStu_email(resultSet.getString("stu_email"));
                stu.setStu_sex(resultSet.getString("stu_sex"));
                stu.setStu_remarks(resultSet.getString("stu_remarks"));
                studentList.add(stu);
            }
            return studentList;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DruidUtils.close(connection,preparedStatement,resultSet);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }
}
